package com.study.notepad.dao;

import com.study.notepad.bean.Note;
import com.study.notepad.util.DBUtils;
import com.study.notepad.util.PageUtil;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

public class NoteDao {

    public List<Note> queryByUserId(Integer userId, PageUtil pageUtil) {
        List<Note> list = new ArrayList<>();
        Connection connectionPool = DBUtils.getConnectionPool();

        PreparedStatement statement = null;
        ResultSet resultSet = null;
        String sql = "select id, title, content, create_time, update_time from note where user_id=? ";

        if (pageUtil != null) {
            sql = sql + " limit ?,? ;";
        }
        try {
            statement = connectionPool.prepareStatement(sql);
            statement.setInt(1, userId);
            if (pageUtil != null) {
                int startItems = pageUtil.getStartItems();
                int pageSize = pageUtil.getPageSize();
                statement.setInt(2, startItems);
                statement.setInt(3, pageSize);
            }
            resultSet = statement.executeQuery();
            while (resultSet.next()) {
                int id = resultSet.getInt(1);
                String title = resultSet.getString(2);
                String content = resultSet.getString(3);
                Date createDate = resultSet.getTimestamp(4);
                Date updateDate = resultSet.getTimestamp(5);

                Note note = new Note();
                note.setId(id);
                note.setTitle(title);
                note.setContent(content);
                note.setCreateDate(createDate);
                note.setUpdateDate(updateDate);
                list.add(note);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBUtils.closeConnection(connectionPool, resultSet, statement);
        }
        return list;
    }

    public Integer deleteById(Integer id) {
        int i = 0;
        Connection connectionPool = DBUtils.getConnectionPool();
        String sql = "delete from note where id = ?";
        PreparedStatement ps = null;
        try {
            ps = connectionPool.prepareStatement(sql);
            ps.setInt(1, id);
            i = ps.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBUtils.closeConnection(connectionPool, null, ps);
        }
        return i;
    }

    public Integer updateById(Integer id, String title, String content) {
        int i = 0;
        Connection connectionPool = DBUtils.getConnectionPool();
        String sql = "update note set title=?, content=? where id = ?";
        PreparedStatement ps = null;
        try {
            ps = connectionPool.prepareStatement(sql);
            ps.setString(1, title);
            ps.setString(2, content);
            ps.setInt(3, id);
            i = ps.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBUtils.closeConnection(connectionPool, null, ps);
        }
        return i;
    }

    public Integer insert(Integer userId, String title, String content) {
        int i = 0;
        Connection connectionPool = DBUtils.getConnectionPool();
        String sql = "insert into note (user_id,title,content) values (?,?,?)";
        PreparedStatement ps = null;
        try {
            ps = connectionPool.prepareStatement(sql);
            ps.setInt(1, userId);
            ps.setString(2, title);
            ps.setString(3, content);
            i = ps.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBUtils.closeConnection(connectionPool, null, ps);
        }
        return i;
    }

    public Note queryById(int id) {
        Note note = new Note();
        Connection connectionPool = DBUtils.getConnectionPool();
        String sql = "select id, title, content, create_time, update_time from note where id=?";
        PreparedStatement statement = null;
        ResultSet resultSet = null;
        try {
            statement = connectionPool.prepareStatement(sql);
            statement.setInt(1, id);
            resultSet = statement.executeQuery();
            while (resultSet.next()) {
                note.setId(resultSet.getInt(1));
                note.setTitle(resultSet.getString(2));
                note.setContent(resultSet.getString(3));
                note.setCreateDate(resultSet.getDate(4));
                note.setUpdateDate(resultSet.getDate(5));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBUtils.closeConnection(connectionPool, resultSet, statement);
        }
        return note;
    }

    public Integer totalNum(Integer userId) {
        Integer total = 0;
        Connection connectionPool = DBUtils.getConnectionPool();
        PreparedStatement statement = null;
        ResultSet resultSet = null;
        String sql = "select count(1) from note where user_id=? ";
        try {
            statement = connectionPool.prepareStatement(sql);
            statement.setInt(1, userId);
            resultSet = statement.executeQuery();
            while (resultSet.next()) {
                total = resultSet.getInt(1);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBUtils.closeConnection(connectionPool, resultSet, statement);
        }
        return total;
    }
}
